use crate::model::db;
use crate::model::request::{User, UserEdit, UserSearch};
use crate::model::response::{UserInfoRes, UserList, UserLoginAdminRes, UserName};
use sqlx::{self};

// 新增
pub async fn create_user(data: &User) -> Result<(), sqlx::Error> {
    let pool = db::get_pool().unwrap();
    sqlx::query("INSERT INTO tb_users (username, password, tel, remark, created_at, updated_at) VALUES (?, ?, ?, ?,NOW(), NOW())")
        .bind(&data.username)
        .bind(&data.password)
        .bind(&data.tel)
        .bind(&data.remark)
        .execute(pool)
        .await?;
    Ok(())
}

// 根据 id 获取用户名
pub async fn get_name(id: u64) -> Result<String, sqlx::Error> {
    let pool = db::get_pool().unwrap();
    let user: UserName = sqlx::query_as("SELECT username FROM tb_users WHERE id = ?")
        .bind(id)
        .fetch_one(pool)
        .await?;
    Ok(user.username)
}

// 根据 name 查询user 信息
pub async fn get_user_info(name: &str) -> Result<UserLoginAdminRes, sqlx::Error> {
    let pool = db::get_pool().unwrap();
    let user: UserLoginAdminRes =
        sqlx::query_as("SELECT id, username, tel,password FROM tb_users WHERE username =?")
            .bind(name)
            .fetch_one(pool)
            .await?;
    Ok(user)
}

// 根据 name 查询 name
pub async fn query_name(name: &str) -> Result<String, sqlx::Error> {
    let pool = db::get_pool().unwrap();
    let user: UserName = sqlx::query_as("SELECT username FROM tb_users WHERE username = ?")
        .bind(name)
        .fetch_one(pool)
        .await?;
    Ok(user.username)
}

// 修改 user
pub async fn edit_user(data: &UserEdit) -> Result<(), sqlx::Error> {
    let pool = db::get_pool().unwrap();
    let sql_text =
        "UPDATE tb_users SET username = ?, tel = ?, remark = ?, updated_at = NOW() WHERE id = ?";
    let res = sqlx::query(&sql_text)
        .bind(&data.username)
        .bind(&data.tel)
        .bind(&data.remark)
        .bind(&data.id)
        .execute(pool)
        .await;

    match res {
        Ok(i) => println!("ok {:#?}", i),
        Err(e) => println!("err {:#?}", e),
    }
    Ok(())
}

// 详情
pub async fn user_detail(id: u64) -> Result<UserInfoRes, sqlx::Error> {
    let pool = db::get_pool().unwrap();
    let res = sqlx::query_as::<_, UserInfoRes>("SELECT * FROM tb_users WHERE id=?")
        .bind(id)
        .fetch_one(pool)
        .await;
    match res {
        Ok(i) => {
            println!("{:#?}", i);
            Ok(i)
        }
        Err(e) => {
            println!("{:#?}", e);
            Err(e)
        }
    }
}

// 删除 topic
pub async fn delete_user(id: u64) -> Result<(), sqlx::Error> {
    let pool = db::get_pool().unwrap();
    let _res = sqlx::query("DELETE FROM tb_users WHERE id=?")
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

// 列表搜索
pub async fn get_users_list(data: &UserSearch) -> Result<UserList, sqlx::Error> {
    let pool = db::get_pool().unwrap();
    let offset = (data.page - 1) * data.page_size;

    // 第一步：构建最基础的查询
    let mut query = "SELECT id,username,tel,remark,created_at,updated_at FROM tb_users".to_string();

    // 构建搜索条件
    let mut conditions = Vec::new();

    if let Some(id) = &data.id {
        conditions.push(format!("id = '{}'", id));
    }

    if let Some(username) = &data.username {
        conditions.push(format!("username LIKE '%{}%'", username));
    }

    if let Some(tel) = &data.tel {
        conditions.push(format!("tel = '{}'", tel));
    }

    // 添加搜索条件到查询语句
    if !conditions.is_empty() {
        query.push_str(" WHERE ");
        query.push_str(&conditions.join(" AND "));
    }

    // 添加排序和分页限制
    query.push_str(" ORDER BY created_at DESC LIMIT ? OFFSET ?");

    println!("sql: {}", query);
    // 执行查询
    let topic_list = sqlx::query_as::<_, UserInfoRes>(&query)
        .bind(data.page_size)
        .bind(offset)
        .fetch_all(pool)
        .await?;

    // 获取总条数
    let count_query = format!(
        "SELECT COUNT(*) as total FROM tb_users {}",
        if !conditions.is_empty() {
            format!("WHERE {}", conditions.join(" AND "))
        } else {
            "".to_string()
        }
    );
    let count_result: (Option<i64>,) = sqlx::query_as(count_query.as_str()).fetch_one(pool).await?;
    let count = count_result.0.unwrap_or(0); // 空结果处理

    let res = UserList {
        list: topic_list,
        total: count,
    };

    Ok(res)
}
